package ai.plantdata.dao.strategy;

import ai.plantdata.dao.dto.AssociateDTO;
import ai.plantdata.dao.util.DataTypeUtil;
import ai.plantdata.dao.util.JdbcUtils;
import ai.plantdata.graph.api.schema.DataType;
import ai.plantdata.graph.api.schema.NumericDefinition;
import ai.plantdata.transform.TypeTransform;
import ai.plantdata.vo.AssociationTable;
import org.springframework.util.CollectionUtils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: WangHui
 * @Date: 2021/7/20 13:36
 * @Description:
 */
public interface Handle {

    /**
     * 获取关联表中某一字段信息
     *
     * @param conn
     * @param associateDTO
     * @param asso
     * @return
     */
    default Map<String, String> getAssociate(Connection conn, AssociateDTO associateDTO, Object asso) {
        try {
            String tableName = associateDTO.getTableName();
            String nameField = associateDTO.getNameField();
            String meanTagField = associateDTO.getMeanTagField();
            String associateField = associateDTO.getAssociateField();
            Map<String, String> map = null;
            String sql;
            if (meanTagField == null) {
                sql = "select %s  from %s where %s = ?";
                sql = String.format(sql, nameField, tableName, associateField);
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setObject(1, asso);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    map = new HashMap<>();
                    map.put("name", rs.getObject(1, String.class));
                }
            } else {
                sql = "select %s , %s from %s where %s = ?";
                sql = String.format(sql, nameField, meanTagField, tableName, associateField);
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setObject(1, asso);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    map = new HashMap<>();
                    map.put("name", rs.getObject(1, String.class));
                    map.put("meanTag", rs.getObject(2, String.class));
                }
            }
            return map;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 从表中读取name与meanTag
     *
     * @param conn
     * @param tableName
     * @param nameField
     * @param meanTagField
     * @return
     */
    default List<Map<String, String>> getToken(Connection conn, String tableName, String nameField, String meanTagField) {
        List<Map<String, String>> list = new ArrayList<>();
        try {
            String sql;
            if (meanTagField == null) {
                sql = "select %s  from %s";
                sql = String.format(sql, nameField, tableName);
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    Map<String, String> map = new HashMap<>();
                    map.put("name", rs.getObject(nameField, String.class));
                    list.add(map);
                }
                JdbcUtils.close(ps, rs);
            } else {
                sql = "select %s , %s from %s";
                sql = String.format(sql, nameField, meanTagField, tableName);
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    Map<String, String> map = new HashMap<>();
                    map.put("name", rs.getObject(nameField, String.class));
                    map.put("meanTag", rs.getObject(meanTagField, String.class));
                    list.add(map);
                }
                JdbcUtils.close(ps, rs);
            }
            conn.close();
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 从表中读取数值属性定义
     *
     * @param conn
     * @param tableName
     * @param fieldNames
     * @return
     */
    default List<NumericDefinition> getNumDef(Connection conn, String tableName, List<String> fieldNames) {
        try {
            List<NumericDefinition> numericDefinitions = new ArrayList<>();
            if (!CollectionUtils.isEmpty(fieldNames)) {
                for (String fieldName : fieldNames) {
                    int type = getTypeClass(tableName, fieldName, conn);
                    Class<?> classType = TypeTransform.jdbcTypeToJavaType(type);
                    int dataType = DataTypeUtil.classToDataType(classType);
                    if (dataType == 5) {
                        String sql = "select %s from %s";
                        sql = String.format(sql, fieldName, tableName);
                        PreparedStatement ps = conn.prepareStatement(sql);
                        ResultSet resultSet = ps.executeQuery();
                        if (resultSet.next()) {
                            String object = resultSet.getObject(1).toString();
                            boolean matches = object.matches("www.[a-zA-z0-9]+.[a-zA-z0-9]+");
                            if (matches) {
                                dataType = 9;
                            }
                        }
                        resultSet.close();
                        ps.close();
                    }
                    NumericDefinition numericDefinition = NumericDefinition.create(fieldName, DataType.getByCode(dataType));
                    numericDefinitions.add(numericDefinition);
                }
            }
            return numericDefinitions;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    default AssociateDTO setAssociateDto(AssociationTable.Table table) {
        AssociateDTO associateDTO = new AssociateDTO();
        associateDTO.setTableName(table.getTableName());
        associateDTO.setNameField(table.getNameField());
        associateDTO.setMeanTagField(table.getMeanTagField());
        associateDTO.setAssociateField(table.getField());
        return associateDTO;
    }


    default int getTypeClass(String tableName, String columnName, Connection conn) {
        try {
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet rs = metaData.getColumns(null, null, tableName, columnName);
            if (rs.next()) {
                return rs.getInt("DATA_TYPE");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    default int getNumDef(Connection conn, String tableName, String fieldName) {
        try {
            int type = getTypeClass(tableName, fieldName, conn);
            Class<?> classType = TypeTransform.jdbcTypeToJavaType(type);
            int dataType = DataTypeUtil.classToDataType(classType);
            if (dataType == 5) {
                String sql = "select %s from %s";
                sql = String.format(sql, fieldName, tableName);
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet resultSet = ps.executeQuery();
                if (resultSet.next()) {
                    String object = resultSet.getObject(1).toString();
                    boolean matches = object.matches("www.[a-zA-z0-9]+.[a-zA-z0-9]+");
                    if (matches) {
                        dataType = 9;
                    }
                }
                JdbcUtils.close(ps, resultSet);
            }
            return dataType;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }


}
